

use "$datatemp/rosstat_allvars.dta", clear
keep if Year==1980
keep Birthplace_code Birthplace numbir
sort Birthplace_code
save "$datatemp/oblast_sample_fertility.dta", replace

use "$datatemp/rosstat_allvars.dta", clear
keep Birthplace_code Year Birthplace numbir numwomen_1544 gfr_official trade concrete brick meat timber canned
rename Year Birthyear
sort Birthplace_code Birthyear
save "$datatemp/rosstat_tomergeto2010.dta", replace


use "$datatemp/birthplace_codes.dta", clear
sort Birthplace
save "$datatemp/birthplace_codes.dta", replace

set excelxlsxlargefile on

* Import data for children's outcomes using the 2010 census
import excel using "$data/birthplace_year_month_educ_2010.xlsx", clear firstrow sheet("educ") cellrange(B11:V150161)
drop if Birthyear==.
sort Birthplace Birthyear Birthmonth 
save "$datatemp/birthplace_year_month_educ_2010.dta", replace

import excel using "$data/birthplace_year_month_marstat_2010.xlsx", clear firstrow sheet("marstat") cellrange(F9:P150159)
drop if Birthyear==.
sort Birthplace Birthyear Birthmonth 
save "$datatemp/birthplace_year_month_marstat_2010.dta", replace

import excel using "$data/birthplace_year_month_numkids_correct_2010.xlsx", clear firstrow sheet("numkids") cellrange(E9:AD150159)
drop if Birthyear==.
sort Birthplace Birthyear Birthmonth 
save "$datatemp/birthplace_year_month_numkids_correct_2010.dta", replace

import excel using "$data/birthplace_year_month_woman_age1stbir_2010.xlsx", clear firstrow sheet("age1stbir") cellrange(E10:Z150160)
drop if Birthyear==.
sort Birthplace Birthyear Birthmonth 
save "$datatemp/birthplace_year_month_woman_age1stbir_2010.dta", replace

import excel using "$data/birthplace_year_month_numbirth_sex_2010.xlsx", clear firstrow sheet("sex") cellrange(E9:J150159)
drop if Birthyear==.
sort Birthplace Birthyear Birthmonth 
save "$datatemp/birthplace_year_month_numbirth_sex_2010.dta", replace

import excel using "$data/birthplace_year_month_empl_sex_2010.xlsx", clear firstrow sheet("empl") cellrange(E10:N150160)
drop if Birthyear==.
sort Birthplace Birthyear Birthmonth 
save "$datatemp/birthplace_year_month_empl_sex_2010.dta", replace

import excel using "$data/birthplace_year_month_posobie_0210.xlsx", clear firstrow sheet("table") cellrange(F10:L150160)
drop if Birthyear==.
sort Birthplace Birthyear Birthmonth
save "$datatemp/birthplace_year_month_posobie_0210.dta", replace

use "$datatemp/birthplace_year_month_educ_2010.dta", clear
sort Birthplace Birthyear Birthmonth
merge (Birthplace Birthyear Birthmonth) using "$datatemp/birthplace_year_month_numbirth_sex_2010.dta"
keep if _merge==3
drop _merge
sort Birthplace Birthyear Birthmonth
merge (Birthplace Birthyear Birthmonth) using "$datatemp/birthplace_year_month_marstat_2010.dta"
keep if _merge==3 
drop _merge
sort Birthplace Birthyear Birthmonth
merge (Birthplace Birthyear Birthmonth) using "$datatemp/birthplace_year_month_numkids_correct_2010.dta"
keep if _merge==3
drop _merge
sort Birthplace Birthyear Birthmonth
merge (Birthplace Birthyear Birthmonth) using "$datatemp/birthplace_year_month_woman_age1stbir_2010.dta"
keep if _merge==3
drop _merge
sort Birthplace Birthyear Birthmonth
merge (Birthplace Birthyear Birthmonth) using "$datatemp/birthplace_year_month_empl_sex_2010.dta"
keep if _merge==3
drop _merge
sort Birthplace Birthyear Birthmonth
merge (Birthplace Birthyear Birthmonth) using "$datatemp/birthplace_year_month_posobie_0210.dta"
keep if _merge==3
drop _merge

* Merge birthplace codes
sort Birthplace
merge (Birthplace) using "$datatemp/birthplace_codes.dta"
keep if _merge==3
drop _merge
drop if Birthyear== 0

collapse (sum) na_educ- posob_yes10 (min) loc, by(Birthplace_code Birthyear Birthmonth)
sort Birthplace_code
merge (Birthplace_code) using "$datatemp/oblast_sample_fertility.dta"
keep if _merge==3
drop _merge
sort Birthplace_code Birthyear 
merge (Birthplace_code Birthyear) using "$datatemp/rosstat_tomergeto2010.dta"
keep if _merge==3
collapse (mean) loc numwomen_1544 trade concrete brick meat timber canned (sum) num_women posob_no10 posob_yes10 numkid_0 numkid_1 numkid_2 numkid_3 numkid_4 numkid_5 numkid_6 numkid_7 numkid_8 numkid_9 numkid_10 numkid_11 numkid_12 numkid_13 numkid_14 numkid_15 numkid_16 numkid_17 numkid_18 numkid_19 numkid_20 empl_man unemp_man empl_wom unemp_wom married cohab widow nevermar divorced separated age1st_13 age1st_14 age1st_15 age1st_16 age1st_17 age1st_18 age1st_19 age1st_20 age1st_21 age1st_22 age1st_23 age1st_24 age1st_25 age1st_26 age1st_27 age1st_28 age1st_29 age1st_30 elem lths hs elem_prof second_prof somecol graduate read noread noeduc noelem_read noelem_noread bacalavr specialist masters, by(Birthplace_code Birthyear) 

gen educ_denom = elem+ lths+ hs+ elem_prof+ second_prof+ somecol+ graduate+ read+ noread +noeduc+ noelem_read+ noelem_noread+ bacalavr+ specialist+masters
gen college_more_per = (graduate+bacalavr+specialist+masters)*100/educ_denom
gen teen_mom = (age1st_13 + age1st_14 +age1st_15 +age1st_16 +age1st_17 +age1st_18 +age1st_19)*100/num_women
gen empl_man_per = empl_man*100/(empl_man+unemp_man)
gen empl_wom_per = empl_wom*100/(empl_wom+unemp_wom)
gen posob10_per=posob_yes10*100/(posob_yes10+posob_no10)
gen numkid_ave = (0*numkid_0 +numkid_1 +numkid_2*2+ 3*numkid_3+ 4*numkid_4+ 5*numkid_5 +6*numkid_6 +7*numkid_7 +8*numkid_8 +9*numkid_9 +10*numkid_10 +11*numkid_11 +12*numkid_12 +13*numkid_13 +14*numkid_14 +15*numkid_15 +16*numkid_16 +17*numkid_17 +18*numkid_18 +19*numkid_19 +20*numkid_20)/(numkid_0 +numkid_1 +numkid_2 +numkid_3 +numkid_4 +numkid_5 +numkid_6 +numkid_7 +numkid_8 +numkid_9 +numkid_10 +numkid_11 +numkid_12 +numkid_13 +numkid_14 +numkid_15 +numkid_16 +numkid_17 +numkid_18 +numkid_19 +numkid_20)
gen married_per = married*100/(married+cohab+widow+nevermar+divorced+separated)
gen educ_yrs = (noeduc*0+noelem_read*0+noelem_noread*0+elem*1+lths*2+hs*3+elem_prof*4+second_prof*5+somecol*6+bacalavr*7+specialist*8+masters*9+graduate*10)/educ_denom

keep Birthyear Birthplace_code loc educ_denom college_more_per teen_mom empl_man_per empl_wom_per posob10_per numkid_ave married_per educ_yrs

label variable Birthyear "Year of birth"
label variable Birthplace_code "Unique numerical code for each oblast of birth"
label variable educ_denom "Number of births in the 2010 census"
label variable college_more_per "Share of individuals who completed at least college"
label variable teen_mom "Share of women who had a child while a teenager"
label variable empl_man_per "Share of employed men"
label variable empl_wom_per "Share of employed women"
label variable posob10_per "Share on public assistance"
label variable numkid_ave "Average number of children"
label variable married_per "Share married"
label variable educ_yrs "Linear education index constructed by author"
label variable loc "equals to 1 for an early beneficiary and 2 for a late beneficiary"

save "$datatemp/outcomes_2010_oblast.dta", replace

